using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using Microsoft.AspNetCore.Http;
using NewLife.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Admin.NET.Application.Excel;

/// <summary>
/// excel操作
/// </summary>
public class ExcelUtil
{
	public MemoryStream GetTemplate(List<string> clmList, Dictionary<string, List<string>> ddlDic, string title)
	{
		string typeSheetName = "Type";
		XSSFWorkbook workbook = new XSSFWorkbook();
		ISheet useSheet = workbook.CreateSheet(title);
		ISheet typeSheet = workbook.CreateSheet(typeSheetName);
		workbook.SetSheetHidden(workbook.GetSheetIndex(typeSheet), hidden: true);
		IRow row = useSheet.CreateRow(0);
		int maxRowNum = ((ddlDic.Count != 0) ? ddlDic.Max((KeyValuePair<string, List<string>> t) => t.Value.Count) : 0);
		for (int i = 0; i < maxRowNum + 1; i++)
		{
			IRow typeRow = typeSheet.CreateRow(i);
		}
		if (clmList == null || clmList.Count == 0)
		{
			throw new Exception("列名集合不能为空");
		}
		ICellStyle headStyle = workbook.CreateCellStyle();
		headStyle.Alignment = HorizontalAlignment.Center;
		IFont font = workbook.CreateFont();
		font.IsBold = true;
		headStyle.SetFont(font);
		for (int i = 0; i < clmList.Count; i++)
		{
			ICell cellheard = row.CreateCell(i);
			cellheard.SetCellValue(clmList[i]);
			cellheard.CellStyle = headStyle;
			if (ddlDic.ContainsKey(clmList[i]))
			{
				IRow tmpRow = typeSheet.GetRow(0);
				tmpRow.CreateCell(i).SetCellValue(clmList[i]);
				string[] tmpArr = ddlDic[clmList[i]].ToArray();
				for (int j = 0; j < tmpArr.Length; j++)
				{
					typeSheet.GetRow(j + 1).CreateCell(i).SetCellValue(tmpArr[j]);
				}
				string colName = GetExcelColumnName(i + 1);
				XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(useSheet as XSSFSheet);
				XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(string.Format("{2}!${0}$2:${0}${1}", colName, tmpArr.Length + 1, typeSheetName));
				CellRangeAddressList cellRegions = new CellRangeAddressList(1, 11500, i, i);
				XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, cellRegions);
				validation.SuppressDropDownArrow = true;
				validation.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
				validation.ShowErrorBox = true;
				useSheet.AddValidationData(validation);
			}
		}
		useSheet.DefaultColumnWidth = 20;
		MemoryStream ms = new MemoryStream();
		workbook.Write(ms);
		return ms;
	}

	/// <summary>
	/// 获取Excel列名
	/// </summary>
	/// <param name="columnNumber">列的序号，如：A、B、C、AA、BB</param>
	/// <returns></returns>
	private string GetExcelColumnName(int columnNumber)
	{
		int dividend = columnNumber;
		string columnName = string.Empty;
		while (dividend > 0)
		{
			int modulo = (dividend - 1) % 26;
			columnName = Convert.ToChar(65 + modulo) + columnName;
			dividend = (dividend - modulo) / 26;
		}
		return columnName;
	}

	public List<T> ExcelToList<T>(IFormFile form, Dictionary<string, string> title = null)
	{
		List<T> list = new List<T>();
		Type typeModel = typeof(T);
		string filePath = form.FileName;
		IWorkbook workbook = null;
		using Stream fs = form.OpenReadStream();
		if (filePath.IndexOf(".xlsx") > 0)
		{
			workbook = new XSSFWorkbook(fs);
		}
		else if (filePath.IndexOf(".xls") > 0)
		{
			workbook = new HSSFWorkbook(fs);
		}
		if (workbook != null)
		{
			ISheet sheet = workbook.GetSheetAt(0);
			IRow cells = sheet.GetRow(0);
			string[] cellsTitle = new string[cells.Count()];
			int indexTitle = 0;
			foreach (ICell item in cells)
			{
				if (title != null)
				{
					if (title.ContainsKey(item.StringCellValue))
					{
						cellsTitle[indexTitle] = title[item.StringCellValue];
					}
					else
					{
						cellsTitle[indexTitle] = "";
					}
				}
				else
				{
					cellsTitle[indexTitle] = item.StringCellValue;
				}
				indexTitle++;
			}
			int rowCount = sheet.LastRowNum;
			for (int i = 1; i <= rowCount; i++)
			{
				T model = Activator.CreateInstance<T>();
				IRow rows = sheet.GetRow(i);
				int cellcount = rows.Cells.Count;
				if (rows == null || cellcount != cellsTitle.Count())
				{
					continue;
				}
				for (int j = 0; j < cellsTitle.Count(); j++)
				{
					ICell value = rows.Cells[j];
					if (cellsTitle[j] == "")
					{
						continue;
					}
					PropertyInfo propertyInfo = typeModel.GetProperty(cellsTitle[j]);
					if (propertyInfo == null)
					{
						continue;
					}
					object objectValue = propertyInfo.PropertyType.CreateInstance();
					switch (value.CellType)
					{
					case CellType.Unknown:
						objectValue = value.StringCellValue;
						break;
					case CellType.Numeric:
					{
						short format = value.CellStyle.DataFormat;
						if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20)
						{
							objectValue = value.DateCellValue;
						}
						else
						{
							objectValue = value.NumericCellValue;
						}
						break;
					}
					case CellType.String:
						objectValue = value.StringCellValue;
						break;
					case CellType.Formula:
						objectValue = value.StringCellValue;
						break;
					case CellType.Boolean:
						objectValue = value.BooleanCellValue;
						break;
					}
					propertyInfo.SetValue(model, GetValueByType(value, propertyInfo.PropertyType));
				}
				list.Add(model);
			}
		}
		return list;
	}

	private object GetValueByType(object value, Type type)
	{
		switch (type.Name)
		{
		case "string":
			return value?.ToString() ?? "";
		case "String":
			return value?.ToString() ?? "";
		case "int":
		{
			if (int.TryParse(value.ToString(), out var sintValue))
			{
				return sintValue;
			}
			return 0;
		}
		case "Int64":
		{
			if (int.TryParse(value.ToString(), out var intValue))
			{
				return intValue;
			}
			return 0L;
		}
		case "double":
		{
			if (double.TryParse(value.ToString(), out var doubleValue))
			{
				return doubleValue;
			}
			return 0.0;
		}
		case "Double":
		{
			if (double.TryParse(value.ToString(), out var bdoubleValue))
			{
				return bdoubleValue;
			}
			return 0.0;
		}
		case "float":
		{
			if (float.TryParse(value.ToString(), out var floatValue))
			{
				return floatValue;
			}
			return 0f;
		}
		case "DateTime":
		{
			if (DateTime.TryParse(value.ToString(), out var datetime))
			{
				return datetime;
			}
			return default(DateTime);
		}
		case "bool":
			if (value is bool)
			{
				return (bool)value;
			}
			return false;
		case "Boolean":
			if (value is bool)
			{
				return (bool)value;
			}
			return false;
		case "Byte":
		{
			if (byte.TryParse(value.ToString(), out var bbyteValue))
			{
				return bbyteValue;
			}
			return (byte)0;
		}
		case "byte":
		{
			if (byte.TryParse(value.ToString(), out var sbyteValue))
			{
				return sbyteValue;
			}
			return (byte)0;
		}
		case "Int32":
		{
			if (int.TryParse(value.ToString(), out var int32Value))
			{
				return int32Value;
			}
			return 0;
		}
		case "Decimal":
		{
			if (decimal.TryParse(value.ToString(), out var decimalValue))
			{
				return decimalValue;
			}
			return 0m;
		}
		default:
			return value?.ToString() ?? "";
		}
	}
}
